import pymysql
class RollDB:
    def __init__(self):
        self.con=None
        self.cursor=None
        self.open()
    def open(self):
        try:
            self.con=pymysql.connect(host="localhost",port=3306,user="root",passwd="rootpwd",db="roll",charset="utf8")
            self.cursor=self.con.cursor(pymysql.cursors.DictCursor)
            print("数据库连接成功!")
            create_table_sql='''
            CREATE TABLE IF NOT EXISTS users (
                id INTEGER PRIMARY KEY AUTO_INCREMENT,
                username TEXT NOT NULL UNIQUE,
                password TEXT NOT NULL
            );
            '''
            self.cursor.execute(create_table_sql)
            self.con.commit()
            create_books_table_sql='''
             CREATE TABLE IF NOT EXISTS books (
                pNo VARCHAR(16) PRIMARY KEY,
                pName VARCHAR(16),
                pAuthor VARCHAR(16),
                pPublishinghouse VARCHAR(16),
                pTime VARCHAR(16)
            );
            '''
            self.cursor.execute(create_books_table_sql)
            self.con.commit()
        except Exception as err:
            print("数据库连接失败",err)
    def close(self):
        self.con.commit()
        self.con.close()
    def clear(self):
        try:
            self.cursor.execute("delete from books")
        except Exception as err:
            print(err)
    def show(self):
        try:
            self.cursor.execute("select * from books where pNo >= 1 and pNo < 10000")
            print("%-16s%-16s%-16s%-16s%-16s"%("编号","图书名称","作者","出版社","出版时间"))
            rows=self.cursor.fetchall()
            for row in rows:
                print("%-16s%-16s%-16s%-16s%-16s"%(row["pNo"],row["pName"],row["pAuthor"],row["pPublishinghouse"],row["pTime"]))
        except Exception as err:
            print(err) 
    def __insert(self,pNo,pName,pAuthor,pPublishinghouse,pTime):
        try:
            sql="insert into books(pNo,pname,pAuthor,pPublishinghouse,pTime) values(%s,%s,%s,%s,%s)"
            self.cursor.execute(sql,(pNo,pName,pAuthor,pPublishinghouse,pTime))
            print(self.cursor.rowcount,"row inserted")
        except Exception as err:
            print(err)
    def __update(self,pNo,pName,pAuthor,pPublishinghouse,pTime):
        try:
            sql="update books set pName=%s,pAuthor=%s,pPublishinghouse=%s,pTime=%s where pNo=%s"
            self.cursor.execute(sql,(pName,pAuthor,pPublishinghouse,pTime,pNo))
            print(self.cursor.rowcount,"row updated")
        except Exception as err:
            print(err)
    def __delete(self,pNo):
        try:
            sql="delete from books where pNo=%s"
            self.cursor.execute(sql,(pNo,))
            print(self.cursor.rowcount,"row deleted")
        except Exception as err:
            print(err)
    def insert__book(self):
        print("(注:图书录入时编号不可以超过10000，否则无法录入！)")
        pNo=input("请输入图书编号:")
        pName=input("请输入图书名称:")
        pAuthor=input("请输入作者:")
        pPublishinghouse=input("请输入出版社:")
        pTime=input("请输入出版时间:")
        if pNo!="" and pName!="":
            self.__insert(pNo,pName,pAuthor,pPublishinghouse,pTime)
            print("图书录入成功！")
        else:
            print("图书编号、图书名称不能为空!")
    def update__book(self):
        pNo=input("请输入图书编号：")
        pName=input("请输入图书名称:")
        pAuthor=input("请输入作者:")
        pPublishinghouse=input("请输入出版社：")
        pTime=input("请输入出版时间：")
        if pNo!="" and pName!="":
            self.__update(pNo,pName,pAuthor,pPublishinghouse,pTime)
            print("图书信息更新成功！")
        else:
             print("图书编号、图书名称不能为空")
    def delete__book(self):
        pNo=input("请输入图书编号：")
        if pNo!="":
            self.__delete(pNo)
            print("图书删除成功！")
        else:
            print("图书编号不能为空！")
    def search__book(self):
        pNo=input("请输入图书编号：")
        if pNo!="":
            try:
                sql="select * from books where pNo=%s"
                self.cursor.execute(sql,(pNo))
                row=self.cursor.fetchone()
                if row:
                    print("图书编号：",row["pNo"])
                    print("图书名称：",row["pName"])
                    print("作者：",row["pAuthor"])
                    print("出版社：",row["pPublishinghouse"])
                    print("出版时间：",row["pTime"])
                else:
                    print("未找到该图书！")
            except Exception as err:
                print(err)
        else:
            print("图书编号不能为空！")
    def search__book__name(self):
        pName=input("请输入图书名称：")
        if pName!="":
            try:
                sql="select * from books where pName like %s"
                self.cursor.execute(sql,("%"+pName+"%"))
                rows=self.cursor.fetchall()
                if rows:
                    for rows in rows:
                        print("图书编号：",rows["pNo"])
                        print("图书名称：",rows["pName"])
                        print("作者：",rows["pAuthor"])
                        print("出版社：",rows["pPublishinghouse"])
                        print("出版时间：",rows["pTime"])
                        print("-------------------------------")
                else:
                    print("未找到该图书！")
            except Exception as err:
                print(err)
        else:
            print("图书名称不能为空！")
    def search__book__author(self):
        pAuthor=input("请输入作者名称：")
        if pAuthor!="":
            try:
                sql="select * from books where pAuthor like %s"
                self.cursor.execute(sql,("%"+pAuthor+"%"))
                rows=self.cursor.fetchall()
                if rows:
                    for rows in rows:
                        print("图书编号：",rows["pNo"])
                        print("图书名称：",rows["pName"])
                        print("作者：",rows["pAuthor"])
                        print("出版社：",rows["pPublishinghouse"])
                        print("出版时间：",rows["pTime"])
                        print("-------------------------------")
                else:
                    print("未找到该图书！")
            except Exception as err:
                print(err)
        else:
            print("作者名称不能为空！")
    def search__book__publishinghouse(self):
        publishinghouse=input("请输入出版社名称：")
        if publishinghouse!="":
            try:
                sql="select * from books where pPublishinghouse like %s"
                self.cursor.execute(sql,("%"+publishinghouse+"%"))
                rows=self.cursor.fetchall()
                if rows:
                    for rows in rows:
                        print("图书编号：",rows["pNo"])
                        print("图书名称：",rows["pName"])
                        print("作者：",rows["pAuthor"])
                        print("出版社：",rows["pPublishinghouse"])
                        print("出版时间：",rows["pTime"])
                        print("-------------------------------")
                else:
                    print("未找到该图书！")
            except Exception as err:
                print(err)
        else:
            print("出版社名称不能为空！")
    def borrow__book(self):
        pNo=input("请输入图书编号：")
        if pNo!="":
            try:
                sql="select * from books where pNo=%s"
                self.cursor.execute(sql,(pNo))
                row=self.cursor.fetchone()
                if row:
                    sql="update books set pNo=pNo+10000 where pNo=%s"
                    self.cursor.execute(sql,(pNo))
                    print("图书借阅成功！")
                else:
                    print("未找到该图书！")
            except Exception as err:
                print(err)
        else:
            print("图书编号不能为空！")
    def return__book(self):
        pNo=input("请输入图书编号：")
        if pNo!="":
            pNo = int(pNo)
            pNo+=10000
            pNo = str(pNo)
            try:
                sql="select * from books where pNo=%s"
                self.cursor.execute(sql,(pNo))
                row=self.cursor.fetchone()
                if row:
                    print("图书归还成功！")
                    sql="update books set pNo=pNo-10000 where pNo=%s"
                    self.cursor.execute(sql,(pNo))
                else:
                    print("未找到该图书！")
                    sql="update books set pNo=pNo-10000 where pNo=%s"
                    self.cursor.execute(sql,(pNo))
            except Exception as err:
                print(err)
        else:
            print("图书编号不能为空！")
    def process(self):
        self.open()
        while True:
            s = input(">")
            if s=="show":
                self.show()
            elif s=="insert":
                self.insert__book()
            elif s=="update":
                self.update__book()
            elif s=="delete":
                self.delete__book()
            elif s=="search":
                self.search__book()
            elif s=="search__name":
                self.search__book__name()
            elif s=="search__author":
                self.search__book__author()
            elif s=="search__publishinghouse":
                self.search__book__publishinghouse()
            elif s=="borrow":
                self.borrow__book()
            elif s=="return":
                self.return__book()
            elif s=="exit":
                break
            else:
                print("可以输入的命令包括: show,insert,update,delete,search,search__name,search__author,search__publishinghouse,borrow,return,exit")
                print("show ---展示所有图书信息")
                print("insert ---添加一本图书")
                print("update ---修改图书信息")
                print("delete ---删除一本图书")
                print("search ---根据编号查找图书")
                print("search__name ---根据图书名称查找图书")
                print("search__author ---根据作者名称查找图书")
                print("search__publishinghouse ---根据出版社名称查找图书")
                print("borrow ---借书")
                print("return ---还书")
                print("exit ---退出")
        self.close()
class user:
     def __init__(self):
        self.con=None
        self.cursor=None
        self.open()
     def open(self):
        try:
            self.con=pymysql.connect(host="localhost",port=3306,user="root",passwd="rootpwd",db="user",charset="utf8")
            self.cursor=self.con.cursor(pymysql.cursors.DictCursor)
            print("数据库连接成功!")
            create_table_sql='''
            CREATE TABLE IF NOT EXISTS users (
                username INTEGER PRIMARY KEY AUTO_INCREMENT,
                password TEXT NOT NULL
            );
            '''
            self.cursor.execute(create_table_sql)
            self.con.commit()
        except Exception as err:
            print("数据库连接失败",err)
     def close(self):
        self.con.commit()
        self.con.close()
     def register(self):
        username=input("请输入用户名：")
        password=input("请输入密码：")
        username = str(username)
        password = str(password)
        if username!="" and password!="":
            try:
                self.cursor.execute("insert into users(username,password) values(%s,%s)",(username,password))
                self.con.commit()
                print("注册成功！欢迎使用图书管理系统！")
            except pymysql.IntegrityError:
                print("用户名已存在，请选择其他用户名。")
        else:
            print("用户名或密码不能为空！请重新输入！")
     def login(self):
        username = input("请输入用户名：")
        password = input("请输入密码：")
        username = str(username)
        password = str(password)
        try:
            self.cursor.execute("select * from users where username=%s and password=%s ", (username, password))
            user=self.cursor.fetchone()
            if user:
                print("登录成功！欢迎使用图书管理系统！")
                return True
            else:
                print("用户名或密码错误！")
                return False
        except Exception as err:
            print("登录时发生错误：", err)
            return False
     def process(self):
        print("欢迎使用图书管理系统！")
        print("请选择注册或登录：(第一次使用请先注册)")
        print("1.注册、2.登录")
        temp=input("请输入选项：")
        if temp=="1":
            self.register()  
        elif temp=="2":
            return
db=user()
db.process()
print("登录")
if db.login() == True:
    print("图书管理系统")
    print("输入help查看帮助")
    db=RollDB()
    db.process()